SSIS/DTSX: Set ‘IsSorted’ property for the source of MergeJoin

Categories: Database; Tagged with: ETL • SSIS; @ December 13th, 2012 15:31

“Merge Join” is a partially blocking component and it requires the source is sorted.

There are two ways to get the source :

1. Use “Sort” component. — Sort is a full blocking component.

2. Sort the source in SQL and mark “IsSorted”

Because Sort is a full blocking component, So I think the second one will get better performance.

I keep the two source sorted, and got one Error:
The IsSorted property must be set to True on both sources of this transformation.

I can not find this property in the properties or Editor, that’s why I hate SSIS sometimes.

At last I got it from “Advanced Editor…”:

and that’s not enough, we need to specify the SortKeyPosition:

Links:

Sort Data for the Merge and Merge Join Transformations http://msdn.microsoft.com/en-us/library/ms137653.aspx

SQL Server Integration Services SSIS Design Best Practices :